import csv
import json
import os
import sys
from typing import List, Dict, Any, Optional
from datetime import datetime
import sqlite3

# 导入之前开发的模块
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from database_connection import DatabaseManager, DatabaseConfig, DatabaseType
from table_manager import TableManager, ColumnInfo
from data_manager import DataManager, QueryOptions


class ImportExportManager:
    """数据导入导出管理器"""

    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
        self.table_manager = TableManager(db_manager)
        self.data_manager = DataManager(db_manager)

    def export_to_csv(self, table_name: str, file_path: str, query_options: QueryOptions = None) -> bool:
        """导出数据到CSV文件"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 获取数据
            results = self.data_manager.select(table_name, options=query_options)

            if not results:
                print(f"表 '{table_name}' 没有数据可导出")
                return False

            # 写入CSV文件
            with open(file_path, 'w', newline='', encoding='utf-8') as csvfile:
                fieldnames = list(results[0].keys())
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

                # 写入表头
                writer.writeheader()

                # 写入数据行
                for row in results:
                    # 处理None值
                    processed_row = {}
                    for key, value in row.items():
                        processed_row[key] = value if value is not None else ""
                    writer.writerow(processed_row)

            print(f"成功导出 {len(results)} 条记录到 {file_path}")
            return True

        except Exception as e:
            print(f"导出CSV失败: {str(e)}")
            return False

    def export_to_json(self, table_name: str, file_path: str, query_options: QueryOptions = None) -> bool:
        """导出数据到JSON文件"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 获取数据
            results = self.data_manager.select(table_name, options=query_options)

            # 准备导出数据
            export_data = {
                "table_name": table_name,
                "export_time": datetime.now().isoformat(),
                "record_count": len(results),
                "data": results
            }

            # 写入JSON文件
            with open(file_path, 'w', encoding='utf-8') as jsonfile:
                json.dump(export_data, jsonfile, indent=2, ensure_ascii=False, default=str)

            print(f"成功导出 {len(results)} 条记录到 {file_path}")
            return True

        except Exception as e:
            print(f"导出JSON失败: {str(e)}")
            return False

    def export_to_sql(self, table_name: str, file_path: str, include_structure: bool = True,
                      query_options: QueryOptions = None) -> bool:
        """导出数据到SQL脚本文件"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            with open(file_path, 'w', encoding='utf-8') as sqlfile:
                # 写入文件头注释
                sqlfile.write(f"-- SQL导出文件\n")
                sqlfile.write(f"-- 表名: {table_name}\n")
                sqlfile.write(f"-- 导出时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
                sqlfile.write(f"-- 数据库类型: {self.db_manager.config.db_type.value}\n\n")

                # 导出表结构
                if include_structure:
                    table_info = self.table_manager.get_table_info(table_name)
                    if table_info:
                        sqlfile.write(f"-- 表结构\n")
                        sqlfile.write(f"DROP TABLE IF EXISTS {table_name};\n")

                        # 构建CREATE TABLE语句
                        column_definitions = []
                        for col in table_info.columns:
                            definition = f"{col.name} {col.data_type}"

                            if col.max_length and col.data_type.upper() in ['VARCHAR', 'CHAR']:
                                definition = f"{col.name} {col.data_type}({col.max_length})"

                            if col.is_primary_key:
                                definition += " PRIMARY KEY"

                            if col.is_auto_increment and self.db_manager.config.db_type == DatabaseType.SQLITE:
                                definition += " AUTOINCREMENT"

                            if not col.is_nullable:
                                definition += " NOT NULL"

                            if col.is_unique and not col.is_primary_key:
                                definition += " UNIQUE"

                            if col.default_value:
                                definition += f" DEFAULT {col.default_value}"

                            column_definitions.append(definition)

                        create_sql = f"CREATE TABLE {table_name} (\n  " + ",\n  ".join(column_definitions) + "\n);\n\n"
                        sqlfile.write(create_sql)

                # 导出数据
                results = self.data_manager.select(table_name, options=query_options)

                if results:
                    sqlfile.write(f"-- 数据插入 ({len(results)} 条记录)\n")

                    columns = list(results[0].keys())

                    for row in results:
                        values = []
                        for col in columns:
                            value = row[col]
                            if value is None:
                                values.append("NULL")
                            elif isinstance(value, str):
                                # 转义单引号
                                escaped_value = value.replace("'", "''")
                                values.append(f"'{escaped_value}'")
                            else:
                                values.append(str(value))

                        insert_sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(values)});\n"
                        sqlfile.write(insert_sql)

                sqlfile.write(f"\n-- 导出完成\n")

            print(f"成功导出SQL脚本到 {file_path}")
            return True

        except Exception as e:
            print(f"导出SQL失败: {str(e)}")
            return False

    def import_from_csv(self, table_name: str, file_path: str, create_table: bool = False,
                        clear_table: bool = False) -> int:
        """从CSV文件导入数据"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not os.path.exists(file_path):
            raise Exception(f"文件不存在: {file_path}")

        try:
            # 读取CSV文件
            with open(file_path, 'r', encoding='utf-8') as csvfile:
                # 自动检测分隔符
                sample = csvfile.read(1024)
                csvfile.seek(0)
                sniffer = csv.Sniffer()
                delimiter = sniffer.sniff(sample).delimiter

                reader = csv.DictReader(csvfile, delimiter=delimiter)
                rows = list(reader)

            if not rows:
                print("CSV文件为空")
                return 0

            # 如果需要创建表
            if create_table:
                self._create_table_from_csv_data(table_name, rows)

            # 如果需要清空表
            if clear_table:
                self.db_manager.execute_non_query(f"DELETE FROM {table_name}")
                print(f"已清空表 '{table_name}'")

            # 批量插入数据
            success_count = self.data_manager.insert_batch(table_name, rows)

            print(f"从CSV文件成功导入 {success_count} 条记录")
            return success_count

        except Exception as e:
            print(f"导入CSV失败: {str(e)}")
            return 0

    def import_from_json(self, file_path: str, target_table: str = None,
                         create_table: bool = False, clear_table: bool = False) -> int:
        """从JSON文件导入数据"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not os.path.exists(file_path):
            raise Exception(f"文件不存在: {file_path}")

        try:
            # 读取JSON文件
            with open(file_path, 'r', encoding='utf-8') as jsonfile:
                data = json.load(jsonfile)

            # 确定表名
            if target_table:
                table_name = target_table
            elif isinstance(data, dict) and 'table_name' in data:
                table_name = data['table_name']
                data = data.get('data', [])
            else:
                raise Exception("无法确定目标表名，请指定target_table参数")

            if not isinstance(data, list):
                raise Exception("JSON数据格式不正确，应该是记录数组")

            if not data:
                print("JSON文件没有数据")
                return 0

            # 如果需要创建表
            if create_table:
                self._create_table_from_json_data(table_name, data)

            # 如果需要清空表
            if clear_table:
                self.db_manager.execute_non_query(f"DELETE FROM {table_name}")
                print(f"已清空表 '{table_name}'")

            # 批量插入数据
            success_count = self.data_manager.insert_batch(table_name, data)

            print(f"从JSON文件成功导入 {success_count} 条记录")
            return success_count

        except Exception as e:
            print(f"导入JSON失败: {str(e)}")
            return 0

    def execute_sql_file(self, file_path: str) -> bool:
        """执行SQL脚本文件"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not os.path.exists(file_path):
            raise Exception(f"文件不存在: {file_path}")

        try:
            with open(file_path, 'r', encoding='utf-8') as sqlfile:
                sql_content = sqlfile.read()

            # 分割SQL语句（简单实现，按分号分割）
            statements = [stmt.strip() for stmt in sql_content.split(';') if stmt.strip()]

            executed_count = 0
            for statement in statements:
                # 跳过注释行
                if statement.startswith('--') or statement.startswith('/*'):
                    continue

                try:
                    self.db_manager.execute_non_query(statement)
                    executed_count += 1
                except Exception as e:
                    print(f"执行SQL语句失败: {statement[:50]}... 错误: {str(e)}")
                    continue

            print(f"成功执行 {executed_count} 条SQL语句")
            return True

        except Exception as e:
            print(f"执行SQL文件失败: {str(e)}")
            return False

    def backup_database(self, backup_path: str, tables: List[str] = None) -> bool:
        """备份数据库"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 获取要备份的表列表
            if tables is None:
                tables = self.db_manager.get_tables()

            backup_data = {
                "backup_time": datetime.now().isoformat(),
                "database_type": self.db_manager.config.db_type.value,
                "tables": {}
            }

            for table_name in tables:
                print(f"备份表: {table_name}")

                # 获取表结构
                table_info = self.table_manager.get_table_info(table_name)

                # 获取表数据
                table_data = self.data_manager.select(table_name)

                backup_data["tables"][table_name] = {
                    "structure": {
                        "columns": [
                            {
                                "name": col.name,
                                "data_type": col.data_type,
                                "is_nullable": col.is_nullable,
                                "is_primary_key": col.is_primary_key,
                                "is_unique": col.is_unique,
                                "default_value": col.default_value,
                                "max_length": col.max_length,
                                "is_auto_increment": col.is_auto_increment
                            }
                            for col in table_info.columns
                        ]
                    },
                    "data": table_data,
                    "row_count": len(table_data)
                }

            # 写入备份文件
            with open(backup_path, 'w', encoding='utf-8') as backup_file:
                json.dump(backup_data, backup_file, indent=2, ensure_ascii=False, default=str)

            total_rows = sum(backup_data["tables"][table]["row_count"] for table in backup_data["tables"])
            print(f"数据库备份完成: {len(tables)} 个表，{total_rows} 条记录")
            print(f"备份文件: {backup_path}")
            return True

        except Exception as e:
            print(f"数据库备份失败: {str(e)}")
            return False

    def restore_database(self, backup_path: str, drop_existing: bool = False) -> bool:
        """恢复数据库"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not os.path.exists(backup_path):
            raise Exception(f"备份文件不存在: {backup_path}")

        try:
            # 读取备份文件
            with open(backup_path, 'r', encoding='utf-8') as backup_file:
                backup_data = json.load(backup_file)

            if "tables" not in backup_data:
                raise Exception("备份文件格式不正确")

            restored_tables = 0
            total_rows = 0

            for table_name, table_info in backup_data["tables"].items():
                print(f"恢复表: {table_name}")

                try:
                    # 如果需要删除现有表
                    if drop_existing:
                        self.table_manager.drop_table(table_name)

                    # 重建表结构
                    columns = []
                    for col_data in table_info["structure"]["columns"]:
                        column = ColumnInfo(
                            name=col_data["name"],
                            data_type=col_data["data_type"],
                            is_nullable=col_data["is_nullable"],
                            is_primary_key=col_data["is_primary_key"],
                            is_unique=col_data["is_unique"],
                            default_value=col_data["default_value"],
                            max_length=col_data["max_length"],
                            is_auto_increment=col_data["is_auto_increment"]
                        )
                        columns.append(column)

                    # 创建表（如果不存在）
                    try:
                        self.table_manager.create_table(table_name, columns)
                    except:
                        # 表可能已存在，继续
                        pass

                    # 清空表数据
                    self.db_manager.execute_non_query(f"DELETE FROM {table_name}")

                    # 恢复数据
                    if table_info["data"]:
                        success_count = self.data_manager.insert_batch(table_name, table_info["data"])
                        total_rows += success_count
                        print(f"  恢复 {success_count} 条记录")

                    restored_tables += 1

                except Exception as e:
                    print(f"恢复表 '{table_name}' 失败: {str(e)}")
                    continue

            print(f"数据库恢复完成: {restored_tables} 个表，{total_rows} 条记录")
            return True

        except Exception as e:
            print(f"数据库恢复失败: {str(e)}")
            return False

    def _create_table_from_csv_data(self, table_name: str, data: List[Dict[str, Any]]):
        """根据CSV数据创建表"""
        if not data:
            return

        # 分析数据类型
        columns = []
        sample_row = data[0]

        for col_name, value in sample_row.items():
            # 简单的类型推断
            if self._is_integer(value):
                data_type = "INTEGER"
            elif self._is_float(value):
                data_type = "REAL"
            else:
                data_type = "TEXT"

            column = ColumnInfo(
                name=col_name,
                data_type=data_type,
                is_nullable=True
            )
            columns.append(column)

        # 添加自增主键
        id_column = ColumnInfo(
            name="id",
            data_type="INTEGER",
            is_primary_key=True,
            is_auto_increment=True,
            is_nullable=False
        )
        columns.insert(0, id_column)

        self.table_manager.create_table(table_name, columns)
        print(f"根据CSV数据创建表 '{table_name}'")

    def _create_table_from_json_data(self, table_name: str, data: List[Dict[str, Any]]):
        """根据JSON数据创建表"""
        if not data:
            return

        # 分析所有记录的字段
        all_fields = set()
        for record in data:
            all_fields.update(record.keys())

        columns = []

        # 为每个字段创建列
        for field_name in sorted(all_fields):
            # 分析字段类型
            sample_values = [record.get(field_name) for record in data[:10] if record.get(field_name) is not None]

            if not sample_values:
                data_type = "TEXT"
            elif all(self._is_integer(v) for v in sample_values):
                data_type = "INTEGER"
            elif all(self._is_float(v) for v in sample_values):
                data_type = "REAL"
            else:
                data_type = "TEXT"

            column = ColumnInfo(
                name=field_name,
                data_type=data_type,
                is_nullable=True
            )
            columns.append(column)

        self.table_manager.create_table(table_name, columns)
        print(f"根据JSON数据创建表 '{table_name}'")

    def _is_integer(self, value) -> bool:
        """检查值是否为整数"""
        try:
            int(value)
            return True
        except (ValueError, TypeError):
            return False

    def _is_float(self, value) -> bool:
        """检查值是否为浮点数"""
        try:
            float(value)
            return True
        except (ValueError, TypeError):
            return False


# 演示和测试代码
if __name__ == "__main__":
    print("数据库管理工具 - 数据导入导出模块测试")
    print("=" * 50)

    # 创建数据库连接
    from database_connection import DatabaseConfig, DatabaseType

    db_manager = DatabaseManager()
    config = DatabaseConfig(
        db_type=DatabaseType.SQLITE,
        database="test_database.db"
    )

    if db_manager.connect(config):
        import_export_manager = ImportExportManager(db_manager)

        # 确保有测试数据
        print("\n准备测试数据...")
        table_manager = TableManager(db_manager)
        data_manager = DataManager(db_manager)

        # 创建测试表
        columns = [
            ColumnInfo(name="id", data_type="INTEGER", is_primary_key=True, is_auto_increment=True, is_nullable=False),
            ColumnInfo(name="name", data_type="VARCHAR", max_length=100, is_nullable=False),
            ColumnInfo(name="email", data_type="VARCHAR", max_length=255, is_unique=True, is_nullable=False),
            ColumnInfo(name="age", data_type="INTEGER"),
            ColumnInfo(name="salary", data_type="REAL"),
            ColumnInfo(name="department", data_type="VARCHAR", max_length=50)
        ]

        table_manager.drop_table("export_test")
        table_manager.create_table("export_test", columns)

        # 插入测试数据
        test_data = [
            {"name": "张三", "email": "zhangsan@test.com", "age": 25, "salary": 8000.0, "department": "开发部"},
            {"name": "李四", "email": "lisi@test.com", "age": 30, "salary": 7000.0, "department": "测试部"},
            {"name": "王五", "email": "wangwu@test.com", "age": 28, "salary": 9000.0, "department": "开发部"},
            {"name": "赵六", "email": "zhaoliu@test.com", "age": 35, "salary": 10000.0, "department": "产品部"}
        ]

        data_manager.insert_batch("export_test", test_data)

        # 测试导出功能
        print("\n测试数据导出...")

        # 导出到CSV
        print("导出到CSV...")
        import_export_manager.export_to_csv("export_test", "export_test.csv")

        # 导出到JSON
        print("导出到JSON...")
        import_export_manager.export_to_json("export_test", "export_test.json")

        # 导出到SQL
        print("导出到SQL...")
        import_export_manager.export_to_sql("export_test", "export_test.sql", include_structure=True)

        # 测试导入功能
        print("\n测试数据导入...")

        # 创建新表用于导入测试
        table_manager.drop_table("import_test_csv")
        table_manager.drop_table("import_test_json")

        # 从CSV导入
        print("从CSV导入...")
        import_export_manager.import_from_csv("import_test_csv", "export_test.csv", create_table=True)

        # 从JSON导入
        print("从JSON导入...")
        import_export_manager.import_from_json("export_test.json", "import_test_json", create_table=True)

        # 验证导入结果
        print("\n验证导入结果...")
        csv_results = data_manager.select("import_test_csv")
        json_results = data_manager.select("import_test_json")

        print(f"CSV导入结果: {len(csv_results)} 条记录")
        print(f"JSON导入结果: {len(json_results)} 条记录")

        # 测试数据库备份和恢复
        print("\n测试数据库备份...")
        backup_success = import_export_manager.backup_database("database_backup.json")

        if backup_success:
            print("\n测试数据库恢复...")
            # 删除一个表来测试恢复
            table_manager.drop_table("export_test")

            # 恢复数据库
            restore_success = import_export_manager.restore_database("database_backup.json")

            if restore_success:
                # 验证恢复结果
                restored_results = data_manager.select("export_test")
                print(f"恢复验证: {len(restored_results)} 条记录")

        # 测试SQL文件执行
        print("\n测试SQL文件执行...")
        import_export_manager.execute_sql_file("export_test.sql")

        db_manager.disconnect()

    print("\n数据导入导出模块测试完成！")
